package com.oceansoft.mobile.biz.api.service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import com.oceansoft.mobile.biz.api.entity.Lawyer;
import com.oceansoft.mobile.biz.api.entity.LawyerRecord;
import com.oceansoft.mobile.common.Constant;
import com.oceansoft.mobile.common.dao.ICommonDao;
import com.oceansoft.mobile.common.dao.IExtendCommonDao;

@Service
public class ApiLawyerRecordServiceImpl implements IApiLawyerRecordService{

	@Resource
	private ICommonDao commonDao;
	@Resource
	private IExtendCommonDao ecommonDao;
	
	@Override
	public void saveLawyer(Lawyer lawyer, HttpServletRequest request) {
		// TODO Auto-generated method stub
		
		lawyer.setAddTime(new Date());
		lawyer.setUpdTime(new Date());
		String insertSql = "insert into wx_lawyer(id,openid,name,mobile,lssName,zyzNum,zyzjgImg,zyzkhImg,swsImg,wtsImg,addTime,updTime) "
				+ " values(sys_guid(),?,?,?,?,?,?,?,?,?,?,?)";
		ecommonDao.update(insertSql, lawyer.getOpenid(), lawyer.getName(),
				lawyer.getMobile(), lawyer.getLssName(), lawyer.getZyzNum(),
				lawyer.getZyzjgImg(), lawyer.getZyzkhImg(), lawyer.getSwsImg(), 
				lawyer.getWtsImg(),lawyer.getAddTime(),lawyer.getUpdTime());
	}

	@Override
	public void saveRecord(LawyerRecord record, HttpServletRequest request) {
		
		String insertSql = "insert into wx_lawyer_record(id,openid,xyrName,xyrCard,status,jjNum,addTime,schedule) "
				+ " values(sys_guid(),?,?,?,?,?,?,?)";
		ecommonDao.update(insertSql,record.getOpenId(),record.getXyrName(),
				record.getXyrCard(),record.getStatus(),record.getJjNum(),new Date(),"1");
	}

	@Override
	public Lawyer queryLawByOpenId(String openid) {
		String sql = "select t.id,"
					+ "t.openid,"
					+ "t.name,"
					+ "t.mobile,"
					+ "t.lssName,"
					+ "t.zyzNum,"
					+ " NVL2(t.zyzjgImg,'1','') as zyzjgImg,"
					+ " NVL2(t.zyzkhImg,'1','') as zyzkhImg,"
					+ " NVL2(t.swsImg,'1','') as swsImg,"
					+ " NVL2(t.wtsImg,'1','') as wtsImg"
					+ " FROM wx_lawyer t WHERE t.openid = ?";
		Lawyer  owner = ecommonDao.loadOne(sql, new RowMapper<Lawyer>() {
				@Override
				public Lawyer mapRow(ResultSet arg0, int arg1) throws SQLException {
					Lawyer lawyer = new Lawyer();
					lawyer.setId(arg0.getString(1));
					lawyer.setOpenid(arg0.getString(2));
					lawyer.setName(arg0.getString(3));
					lawyer.setMobile(arg0.getString(4));
					lawyer.setLssName(arg0.getString(5));
					lawyer.setZyzNum(arg0.getString(6));
					lawyer.setZyzjgImg(arg0.getString(7));
					lawyer.setZyzkhImg(arg0.getString(8));
					lawyer.setSwsImg(arg0.getString(9));
					lawyer.setWtsImg(arg0.getString(10));
					
					return lawyer;
				}
			}, openid);
		return owner;
	}
	@Override
	public Lawyer queryLawByOpenId2(String openid) {
		String sql = "select t.id,"
				+ "t.openid,"
				+ "t.name,"
				+ "t.mobile,"
				+ "t.lssName,"
				+ "t.zyzNum,"
				+ "t.zyzjgImg,"
				+ "t.zyzkhImg,"
				+ "t.swsImg,"
				+ "t.wtsImg,"
				+ "t.addTime,"
				+ "t.updTime "
				+ " FROM wx_lawyer t WHERE t.openid = ?";
	Lawyer owner = ecommonDao.loadOne(sql, new RowMapper<Lawyer>() {
		@Override
		public Lawyer mapRow(ResultSet arg0, int arg1) throws SQLException {
			Lawyer lawyer = new Lawyer();
			lawyer.setId(arg0.getString(1));
			lawyer.setOpenid(arg0.getString(2));
			lawyer.setName(arg0.getString(3));
			lawyer.setMobile(arg0.getString(4));
			lawyer.setLssName(arg0.getString(5));
			lawyer.setZyzNum(arg0.getString(6));
			lawyer.setZyzjgImg(arg0.getString(7));
			lawyer.setZyzkhImg(arg0.getString(8));
			lawyer.setSwsImg(arg0.getString(9));
			lawyer.setWtsImg(arg0.getString(10));
			
			return lawyer;
		}
	}, openid);
	
	return owner;
	}
	@Override
	public void updateLawyer(Lawyer lawyer, HttpServletRequest request) {
		
		lawyer.setUpdTime(new Date());
		StringBuffer sql = new StringBuffer();
		sql.append("update wx_lawyer set "
				+ " name= :name,"
				+ " mobile=:mobile,"
				+ " lssName=:lssName,"
				+ " zyzNum=:zyzNum ,"
				+ " updTime=:updTime");
		if(!"".equals(lawyer.getZyzjgImg())){
			sql.append(" ,zyzjgImg=:zyzjgImg ");
		}
		if(!"".equals(lawyer.getZyzkhImg())){
			sql.append(" ,zyzkhImg=:zyzkhImg ");
		}
		if(!"".equals(lawyer.getSwsImg())){
			sql.append(" ,swsImg=:swsImg ");
		}
		if(!"".equals(lawyer.getWtsImg())){
			sql.append(" ,wtsImg=:wtsImg ");
		}
		sql.append(" WHERE openid=:openid");
		
		ecommonDao.updateOne(sql.toString(),lawyer);
		
	}

	@Override
	public LawyerRecord queryRecord(LawyerRecord record) {
		String sql = "select t.id,"
				+ " t.openId,"
				+ " t.xyrName,"
				+ " t.xyrCard, "
				+ " t.jjNum,"
				+ " t.addTime, "
				+ " t.status"
				+ " FROM wx_lawyer_record t WHERE t.openid = ? and t.jjNum= ?";
		LawyerRecord lrecord = ecommonDao.loadOne(sql, new RowMapper<LawyerRecord>() {
		@Override
		public LawyerRecord mapRow(ResultSet arg0, int arg1) throws SQLException {
			LawyerRecord record = new  LawyerRecord();
			record.setId(arg0.getString(1));
			record.setOpenId(arg0.getString(2));
			record.setXyrName(arg0.getString(3));
			record.setXyrCard(arg0.getString(4));
			record.setJjNum(arg0.getString(5));
			record.setStatus(arg0.getString(7));
			return record;
		}
	}, record.getOpenId(),record.getJjNum());
	
	return lrecord;
		
	}

	@Override
	public List<LawyerRecord> queryRecordList(String xyrName, String xyrCard,
			String beginTime, String endTime,HttpServletRequest request) {
		HttpSession session = request.getSession();
		Lawyer law = (Lawyer)session.getAttribute(Constant.SESSION_LAWYER);
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT t.id,t.openId,t.xyrName,t.xyrCard,t.jjNum,t.addTime,t.status,to_char(t.addTime, 'yyyy-mm-dd') AS addTimeStr,t.schedule "
				+ " FROM wx_lawyer_record t"
				+ " WHERE 1=1"
				+ " and t.openid = ?");
		if(!"".equals(xyrName)){
			sql.append(" and t.xyrName LIKE '%"+xyrName+"%'");
		}
		if(!"".equals(xyrCard)){
			sql.append(" and t.xyrCard = '"+xyrCard+"'");
		}
		if(!"".equals(beginTime)){
//			sql.append(" and date_format(t.addTime,'%Y-%m-%d') > '"+beginTime+"'");
			sql.append(" and to_char(t.addTime,'yyyy-mm-dd') >= '"+beginTime+"'");
		}
		if(!"".equals(endTime)){
//			sql.append(" and date_format(t.addTime,'%Y-%m-%d') < '"+endTime+"'");
			sql.append(" and to_char(t.addTime,'yyyy-mm-dd') <= '"+endTime+"'");
		}
		sql.append("order by t.addTime desc");
		System.out.println(sql.toString());
		List<LawyerRecord> list = ecommonDao.loadList(sql.toString(), new RowMapper<LawyerRecord>() {
			@Override
			public LawyerRecord mapRow(ResultSet arg0, int arg1) throws SQLException {
				LawyerRecord record = new  LawyerRecord();
				record.setId(arg0.getString(1));
				record.setOpenId(arg0.getString(2));
				record.setXyrName(arg0.getString(3));
				record.setXyrCard(arg0.getString(4));
				record.setJjNum(arg0.getString(5));
				record.setAddTime(arg0.getDate(6));
				record.setStatus(arg0.getString(7));
				record.setAddTimeStr(arg0.getString(8));
				record.setSchedule(arg0.getString(9));
				return record;
			}
		},law.getOpenid());
		return list;
	}

	

}
